***BIRDING TRAILS***
*Cleaning dataset*

/*
***Import raw data to stata
clear
set mo off
import delimited \\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\eBird_data\ebd_US_relFeb-2017.txt //import ebird csv data
//(47 vars, 274,141,077 obs)
sort observationdate

save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\eBird_data\ebird_data_complete_USA.dta", replace //save full dataset
*/

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\eBird_data\ebird_data_complete_USA.dta"

*** drop unecessary variables to make dataset smaller
drop v47 speciescomments tripcomments reason reviewed hasmedia projectcode protocoltype samplingeventidentifier lastname firstname longitude latitude localitytype locality atlasblock usfwscode bcrcode county agesex breedingbirdatlascode subspeciesscientificname subspeciescommonname category taxonomicorder lastediteddate country countrycode effortdistancekm effortareaha approved
*** drop interesting variables which are not useful for this specific study
drop globaluniqueidentifier scientificname countycode ibacode durationminutes allspeciesreported groupidentifier

***drop observations with missing dates & missing observers*
drop if missing(observerid) //use "if missing(x)" rather than "if x == ." for strings
gen observer = substr(observerid, 5, .)
destring observer, replace
label var observer "unique birder ID number"

gen date = date(observationdate, "YMD") //generate a numeric date var (original var is string)
format date %td //format date variable so it is readable
drop if date == .
gen year = year(date)
label var date "observation date"
label var year "year"

*** drop years before 1990 (unnecessary for this project)
drop if date <(td(1jan1990))

***create variable grouping observations (species) into the checklists they were submitted as
egen checklist = group(observer date statecode localityid timeobservationsstarted) 
label var checklist "checklist number"

***create variables for birders' home states/counties
egen homestateyear = mode(statecode), min by(observerid year) 
//creating variable for each individual birder's home state (the state in which they have the most reports)
label var homestateyear "each birder's home state (the state in which they have the most checklists) in a given year"
//egen homecountyyear = mode(countycode), min by(observerid year) 
//creating variable for each individual birder's home county (the county in which they have the most reports)
//label var homecountyyear "each birder's home county (the county in which they have the most checklists) in a given year"

save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta", replace //save trimmed dataset

***keep only observations of 'real' species
drop if commonname == "Acanthis/Spinus sp."
drop if commonname == "Accipiter sp."
drop if commonname == "Acridotheres sp."
drop if commonname == "African Collard-Dove (Domestic type)"
drop if commonname == "Alder/Willow Flycatcher (Traill's Flycatcher)"
drop if commonname == "Amazona sp."
drop if commonname == "American Black Duck/Mallard"
drop if commonname == "American Black/Mottled Duck"
drop if commonname == "Bullock's/Black-backed Oriole"
drop if commonname == "Buteo sp." 
drop if commonname == "Buteo/eagle sp."
drop if commonname == "Cackling/Canada Goose"
drop if commonname == "Calidris sp."
drop if commonname == "California/Gambel's Quail"
drop if commonname == "California/Woodhouse's Scrub-Jay" 
drop if commonname == "Canyon/Abert's Towhee"
drop if commonname == "Caprimulgus sp."
drop if commonname == "Carduelis sp."
drop if commonname == "Carolina/Black-capped Chickadee"
drop if commonname == "Cassin's/Blue-headed Vireo"
drop if commonname == "Cassin's/Plumbeous Vireo"
drop if commonname == "Cassin's/Western Kingbird"
drop if commonname == "Cathartes sp."
drop if commonname == "Catharus sp."
drop if commonname == "Chaetura sp."
drop if commonname == "Chimney/Vaux's Swift"
drop if commonname == "Chlidonias sp."
drop if commonname == "Cliff/Cave Swallow"
drop if commonname == "Coccyzus sp." 
drop if commonname == "Cockatiel (Domestic type)"
drop if commonname == "Columba sp."
drop if commonname == "Common Ringed/Semipalmated Plover"
drop if commonname == "Common/Arctic Tern"
drop if commonname == "Common/Barrow's Goldeneye"
drop if commonname == "Common/Black Scoter"
drop if commonname == "Common/Forster's Tern"
drop if commonname == "Common/Hoary Redpoll"
drop if commonname == "Common/Red-breasted Merganser"

drop if commonname == "Common/Ruddy Ground-Dove"
drop if commonname == "Common/Spotted Sandpiper"
drop if commonname == "Common/Thick-billed Murre"
drop if commonname == "Common/Yellow-billed Loon"
drop if commonname == "Connecticut/MacGillivray's/Mourning Warbler"
drop if commonname == "Cookilaria petrel sp." 
drop if commonname == "Cooper's Hawk/Northern Goshawk"
drop if commonname == "Cory's/Cape Verde Shearwater"
drop if commonname == "Cory's/Great Shearwater"
drop if commonname == "Crested/Southern Caracara"
drop if commonname == "Cuculus sp."
drop if commonname == "Curve-billed/Bendire's Thrasher"
drop if commonname == "Cypseloides sp."
drop if commonname == "Acanthis/Spinus sp."
drop if commonname == "Dark-eyed/Yellow-eyed Junco"
drop if commonname == "Downy/Hairy Woodpecker"
drop if commonname == "Dusky/Naumann's Thrush"
drop if commonname == "Dusky/Sooty Grouse"
drop if commonname == "Empidonax sp."
drop if commonname == "Eurasian Moorhen/Common Gallinule"
drop if commonname == "Eurasian/African Collared-Dove"
drop if commonname == "Eurasian/American Wigeon"
drop if commonname == "Eurasian/Oriental Skylark"
drop if commonname == "European/Spotless Starling"
drop if commonname == "Gargany/Green-winged Teal"
drop if commonname == "Geranoaetus/Buteo sp."
drop if commonname == "Golden-fronted/Red-bellied Woodpecker"
drop if commonname == "Golden-winged/Blue-winged Warbler"
drop if commonname == "Golden/Bald Eagle"
drop if commonname == "Grey-tailed/Wandering Tattler" 
drop if commonname == "Gray/Dusky Flycatcher"
drop if commonname == "Graylag Goose (Domestic type)" 
drop if commonname == "Great/Double-crested Cormorant"
drop if commonname == "Greater/Gunnison Sage-Grouse"
drop if commonname == "Greater/Lesser Prairie-Chicken"
drop if commonname == "Greater/Lesser Scaup"
drop if commonname == "Greater/Lesser White-fronted Goose"
drop if commonname == "Hammond's/Dusky Flycatcher"
drop if commonname == "Helmeted Guineafowl (Domestic type)" 

drop if commonname == "Hepatic/Summer Tanager"
drop if commonname == "Herring/Glaucous-winged Gull"
drop if commonname == "Herring/Lesser Black-backed Gull"
drop if commonname == "Herring/Thayer's Gull"
drop if commonname == "Horned/Eared Grebe" 
drop if commonname == "House/Eurasian Tree Sparrow" 
drop if commonname == "House/Purple Finch" 
drop if commonname == "Indian Peafowl (Domestic type)"
drop if commonname == "Island Canary (Domestic type)"
drop if commonname == "Kentish/Snowy Plover" 
drop if commonname == "King/Clapper Rail"
drop if commonname == "King/Common Eider"
drop if commonname == "King/Virginia Rail"
drop if commonname == "Ladder-backed/Nuttall's Woodpecker"
drop if commonname == "Larus sp."
drop if commonname == "Laughing/Franklin's Gull"
drop if commonname == "Lazuli/Indigo Bunting"
drop if commonname == "Leach's/Townsend's Storm-Petrel (dark-rumped)"
drop if commonname == "Leach's/Townsend's Storm-petrel (white-rumped)" 
drop if commonname == "Leach's/Townsend's Storm-Petrel"
drop if commonname == "Leach's/Townsend's/Ainley's Storm-Petrel"
drop if commonname == "Lesser/Greater Roadrunner"
drop if commonname == "Lesser/Greater Sand-Plover" 
drop if commonname == "Lincoln's/Swamp Sparrow"
drop if commonname == "Little/Least Tern"
drop if commonname == "Loggerhead/Northern Shrike"
drop if commonname == "Lonchura sp."
drop if commonname == "Long-eared/Short-eared Owl"

drop if commonname == "Louisiana/Northern Waterthrush"
drop if commonname == "MacGillivray's/Mourning Warbler"
drop if commonname == "Mallard (Domestic type)"
drop if commonname == "Mallard/Mottled Duck" 
drop if commonname == "Masked/Nazca Booby" 
drop if commonname == "Masked/Ruddy Duck"
drop if commonname == "Mississippi/Plumbeous Kite"
drop if commonname == "Mitred/Red-masked Parakeet"
drop if commonname == "Neotropic/Double-crested Cormorant"
drop if commonname == "Northern Cardinal/Pyrrhuloxia"
drop if commonname == "Northern/Gilded Flicker"
drop if commonname == "Northern/Tropical Parula"
drop if commonname == "Orchard/Hooded Oriole"
drop if commonname == "Oreothlypis sp."
drop if commonname == "Oceanodroma sp."
drop if commonname == "Oceanodroma sp. (Band-rumped complex)"
drop if commonname == "Oak/Juniper Titmouse (Plain Titmouse)" 
drop if commonname == "Pacific-slope/Cordilleran Flycatcher"
drop if commonname == "Pacific/Winter Wren"
drop if commonname == "Parid sp."
drop if commonname == "Passer sp."
drop if commonname == "Passerina sp."
drop if commonname == "Petrochelidon sp."
drop if commonname == "Peucaea sp."
drop if commonname == "Pheucticus sp."
drop if commonname == "Philadelphia/Red-eyed Vireo"
drop if commonname == "Philadelphia/Warbling Vireo"

drop if commonname == "Phylloscopus sp."
drop if commonname == "Picoides sp." 
drop if commonname == "Pomarine/Parasitic Jaeger"
drop if commonname == "Ploceus sp."
drop if commonname == "Podiceps sp."
drop if commonname == "Procellariid sp." 
drop if commonname == "Psittacara sp."
drop if commonname == "Psittacula sp." 
drop if commonname == "Pterodroma sp."
drop if commonname == "Purple/Cassin's Finch"
drop if commonname == "Rallus sp."
drop if commonname == "Red Junglefowl (Domestic type)"
drop if commonname == "Red-naped/Red-breasted Sapsucker"
drop if commonname == "Red-necked/Little Stint"
drop if commonname == "Red-necked/Red Phalarope"
drop if commonname == "Red-shouldered/Broad-winged Hawk"
drop if commonname == "Red-winged/Tricolored Blackbird"
drop if commonname == "Redhead x scaup sp. (hybrid)"
drop if commonname == "Ridgway's/Mangrove/Clapper Rail"
drop if commonname == "Ring-necked Duck x scaup sp. (hybrid)"
drop if commonname == "Rock/Band-tailed Pigeon"
drop if commonname == "Rose-breasted/Black-headed Grosbeak"
drop if commonname == "Royal/Elegant Tern"
drop if commonname == "Ruby-throated/Black-chinned Hummingbird"
drop if commonname == "Ruffed/Spruce Grouse"
drop if commonname == "Rufous/Allen's Hummingbird"
drop if commonname == "Rusty/Brewer's Blackbird"
drop if commonname == "Sagebrush/Bell's Sparrow (Sage Sparrow)"
drop if commonname == "Scripps's/Craveri's Murrelet"
drop if commonname == "Scripps's/Guadalupe/Craveri's Murrelet"
drop if commonname == "Sedge/Marsh Wren"
drop if commonname == "Semipalmated/Western Sandpiper"
drop if commonname == "Sharp-shinned/Cooper's Hawk"
drop if commonname == "Sharp-tailed/Pectoral Sandpiper" 
drop if commonname == "Shiny/Brown-headed Cowbird" 
drop if commonname == "Short-billed/Long-billed Dowitcher"
drop if commonname == "Smooth-billed/Groove-billed Ani"
drop if commonname == "Snow/Ross's Goose" 

drop if commonname == "Snow/Ross's x Cackling/Canada Goose (hybrid)"
drop if commonname == "Sooty/Bridled Tern"
drop if commonname == "Sooty/Short-tailed Shearwater"
drop if commonname == "Spinus sp."
drop if commonname == "Spinus sp. (goldfinch sp.)"
drop if commonname == "Spizella sp."
drop if commonname == "Streptopelia sp."
drop if commonname == "Summer/Scarlet Tanager"
drop if commonname == "Surf/Black Scoter"
drop if commonname == "Swan Goose (Domestic type)"
drop if commonname == "Swan x Graylag Goose (Domestic type) (hybrid)"
drop if commonname == "Taiga/Tundra Bean-Goose"
drop if commonname == "Thayer's/Iceland Gull"
drop if commonname == "Townsend's/Hermit Warbler"
drop if commonname == "Tree/Olive-backed Pipit"
drop if commonname == "Tree/Violet-green Swallow"
drop if commonname == "Tringa sp."
drop if commonname == "Tropical/Couch's Kingbird"
drop if commonname == "Trumpeter/Tundra Swan"
drop if commonname == "Tufted Duck x scaup sp. (hybrid)"
drop if commonname == "Tufted/Black-crested Titmouse"
drop if commonname == "Tundra/Whooper Swan"
drop if commonname == "Turdus sp."
drop if commonname == "Western/Clark's Grebe"
drop if commonname == "Western/Eastern Meadowlark"
drop if commonname == "Western/Eastern Wood-Peewee"
drop if commonname == "Western/Eastern Yellow Wagtail"
drop if commonname == "Western/Glaucous-winged Gull"
drop if commonname == "Western/Yellow-footed Gull"
drop if commonname == "White-capped/Salvin's/Chatham Albatross"
drop if commonname == "White-crested/Small-billed Elaenia"
drop if commonname == "White-crowned/White-throated Sparrow"
drop if commonname == "White-winged/Yellow-chevroned Parakeet"
drop if commonname == "Wild Turkey (Domestic type)"
drop if commonname == "Wilson's/Common Snipe"
drop if commonname == "Yellow-bellied/Red-naped Sapsucker"
drop if commonname == "Yellow-billed/Black-billed Cuckoo"

drop if commonname == "Zino's/Fea's Petrel"
drop if commonname == "Zonotrichia sp."
drop if commonname == "Zosterops sp."
drop if commonname == "albatross sp." 
drop if commonname == "alcid sp."
drop if commonname == "auklet sp." 
drop if commonname == "bird sp."
drop if commonname == "black-and-white shearwater sp."
drop if commonname == "blackbird sp."
drop if commonname == "bluebird sp."
drop if commonname == "booby sp."
drop if commonname == "canary sp."
drop if commonname == "chickadee sp."
drop if commonname == "coot sp."
drop if commonname == "corella/cockatoo sp."
drop if commonname == "cormorant sp."
drop if commonname == "crane sp."
drop if commonname == "crossbill sp."
drop if commonname == "crow sp."
drop if commonname == "crow/raven sp."
drop if commonname == "cuckoo sp. (Cuculidae sp.)"
drop if commonname == "curlew sp."
drop if commonname == "dabbling duck sp."
drop if commonname == "diurnal raptor sp."
drop if commonname == "duck sp." 
drop if commonname == "eagle sp."
drop if commonname == "eider sp."
drop if commonname == "elaenia sp. (genus Elaenia)"
drop if commonname == "estrildid finch sp."
drop if commonname == "falcon sp."
drop if commonname == "finch sp."
drop if commonname == "flamingo sp."
drop if commonname == "flycatcher sp. (Tyrannidae)"
drop if commonname == "francolin sp." 
drop if commonname == "frigatebird sp."
drop if commonname == "gallinule/moorhen/coot sp."
drop if commonname == "gnatcatcher sp." 
drop if commonname == "godwit sp."
drop if commonname == "golden-plover sp."

drop if commonname ==  "golden-plover sp."
drop if commonname == "goldeneye sp. x Hooded merganser (hybrid)"
drop if commonname == "goose sp."
drop if commonname == "grackle sp."
drop if commonname == "grebe sp."
drop if commonname == "ground-dove/Inca Dove sp."
drop if commonname == "grouse sp."
drop if commonname == "gull sp."
drop if commonname == "gull/tern sp."
drop if commonname == "harrier sp."
drop if commonname == "hawk sp."
drop if commonname == "heron sp."
drop if commonname == "hummingbird sp."
drop if commonname == "ibis sp."
drop if commonname == "jaeger sp."
drop if commonname == "jaeger/skua sp."
drop if commonname == "jay sp."
drop if commonname == "kingfisher sp." 
drop if commonname == "kinglet sp."
drop if commonname == "large albatross sp."
drop if commonname == "large alcid sp."
drop if commonname == "large falcon sp."
drop if commonname == "large macaw sp."
drop if commonname == "large parakeet sp. (former Aratinga sp.)"
drop if commonname == "large rail sp."
drop if commonname == "large shorebird sp."
drop if commonname == "large swift sp."
drop if commonname == "large tern sp."
drop if commonname ==  "longspur sp."

drop if commonname ==  "loon sp."
drop if commonname ==  "lovebird sp." 
drop if commonname ==  "martin sp."
drop if commonname ==  "merganser sp."
drop if commonname ==  "murrelet sp."
drop if commonname ==  "new world oriole sp." 
drop if commonname ==  "new world quail sp."
drop if commonname ==  "new world vulture sp."
drop if commonname ==  "nighthawk sp."
drop if commonname ==  "nightjar sp." 
drop if commonname ==  "nuthatch sp."
drop if commonname ==  "old world quail sp." 
drop if commonname ==  "owl sp."
drop if commonname ==  "oystercatcher sp." 
drop if commonname ==  "parakeet sp."
drop if commonname ==  "parrot sp."
drop if commonname ==  "passerine sp."
drop if commonname ==  "peep sp."
drop if commonname ==  "pelican sp."
drop if commonname ==  "pewee sp."
drop if commonname ==  "phalarope sp." 
drop if commonname ==  "pheasant sp."
drop if commonname ==  "phoebe sp."
drop if commonname ==  "pigeon/dove sp."
drop if commonname ==  "pipit sp."
drop if commonname ==  "ptarmigan sp."
drop if commonname ==  "plover sp." 
drop if commonname ==  "puffin sp."
drop if commonname ==  "quail-dove sp."
drop if commonname ==  "rail/crake sp."
drop if commonname ==  "raven sp."

drop if commonname ==  "redpoll sp."
drop if commonname ==  "rosefinch sp."
drop if commonname ==  "rosy-finch sp."
drop if commonname ==  "sapsucker sp."
drop if commonname ==  "scoter sp."
drop if commonname ==  "screech-owl sp."
drop if commonname ==  "shearwater sp."
drop if commonname ==  "shorebird sp."
drop if commonname ==  "shrike sp."
drop if commonname ==  "sierra-finch sp."
drop if commonname ==  "skua sp."
drop if commonname ==  "small albatross sp."
drop if commonname ==  "small bittern sp."
drop if commonname ==  "small falcon sp."
drop if commonname ==  "small plover sp."
drop if commonname ==  "small swift sp."
drop if commonname ==  "snipe sp."
drop if commonname ==  "solitary vireo sp."
drop if commonname ==  "sparrow sp."
drop if commonname ==  "storm-petrel sp."
drop if commonname ==  "storm-peterl sp (dark-rumped)"
drop if commonname ==  "sulid sp."
drop if commonname ==  "swallow sp."
drop if commonname ==  "swamphen sp."
drop if commonname ==  "swan sp."
drop if commonname ==  "swift sp."
drop if commonname ==  "tanager sp. (Piranga sp.)"
drop if commonname ==  "tanager sp. (Thraupidae sp.)"
drop if commonname ==  "teal sp."
drop if commonname ==  "tern sp."
drop if commonname ==  "thrush sp."
drop if commonname ==  "towhee sp."
drop if commonname ==  "tropicbird sp."
drop if commonname ==  "vireo sp."
drop if commonname ==  "wagtail sp."
drop if commonname ==  "warbler sp. (Parulidae sp.)"
drop if commonname ==  "waterfowl sp."
drop if commonname ==  "waxbill sp."
drop if commonname ==  "whistling-duck sp."
drop if commonname ==  "white egret sp."
drop if commonname ==  "white-winged gull"
drop if commonname ==  "woodpecker sp."
drop if commonname ==  "wren sp."
drop if commonname ==  "yellow-bellied kingbird sp."
drop if commonname ==  "yellowthroat sp."

drop if commonname ==  "American Robin/Varied Thrush"
drop if commonname ==  "American Three-toed/Black-backed Woodpecker"
drop if commonname ==  "American Tree Sparrow/Spizella sp."
drop if commonname ==  "American/Greater Flamingo"
drop if commonname ==  "American/Northwestern Crow"
drop if commonname ==  "American/Pacific Golden-Plover (Lesser)"
drop if commonname ==  "Anna's/Costa's Hummingbird"
drop if commonname ==  "Arctic/Kamchatka Leaf Warbler"
drop if commonname ==  "Arctic/Pacific Loon"
drop if commonname ==  "Ardea sp."
drop if commonname ==  "Asio sp."
drop if commonname ==  "Aythya sp."
drop if commonname ==  "Aztec/King Rail"
drop if commonname ==  "Bank Swallow/Pale Sand Martin"
drop if commonname ==  "Bay-breasted/Blackpoll Warbler"
drop if commonname ==  "Black-bellied Plover/golden-plover sp."
drop if commonname ==  "Black-capped/Mountain Chickadee"
drop if commonname ==  "Black-crowned/Yellow-crowned Night-Heron"
drop if commonname ==  "Black-legged/Red-legged Kittiwake"
drop if commonname ==  "Black-throated Gray/Townsend's Warbler"
drop if commonname ==  "Black-throated Gray/Hermit Warbler"
drop if commonname ==  "Black/Pigeon Guillemot"
drop if commonname ==  "Blue-winged/Cinnamon Teal"
drop if commonname ==  "Boat-tailed/Great-tailed Grackle"
drop if commonname ==  "Bohemian/Cedar Waxwing"
drop if commonname ==  "Botteri's/Cedar Waxwing"
drop if commonname ==  "Bronzed/Brown-headed Cowbird"
drop if commonname ==  "Brotogeris sp."
drop if commonname ==  "Brown/Black Noddy"
drop if commonname ==  "Brown/Long-billed Thrasher"
drop if commonname ==  "Budgerigar (Domestic type)"
drop if commonname ==  "Bullock's/Baltimore Oriole"
drop if commonname ==  "Domestic goose sp. (Domestic type)"
drop if commonname ==  "Eastern/Mexican Whip-poor-will"
drop if commonname ==  "Euclectes sp."
drop if commonname ==  "Gray-cheeked/Bicknell's Thrush"
drop if commonname ==  "Greater/Lesser Yellowlegs"
drop if commonname ==  "Leach's/Townsend's Storm-Petrel (white-rumped)"
drop if commonname ==  "Melanerpes sp."
drop if commonname ==  "Mimidae sp."
drop if commonname ==  "Myiarchus sp."
drop if commonname ==  "Myioborus sp."
drop if commonname ==  "Setophaga sp."
drop if commonname ==  "Scolopacidae sp."
drop if commonname ==  "Selasphorus sp."
drop if commonname ==  "Sterna sp."
drop if commonname ==  "Toxostoma sp."
drop if commonname ==  "goldeneye sp. x Hooded Merganser (hybrid)"
drop if commonname ==  "storm-petrel sp. (dark-rumped)"
drop if commonname ==  "storm-petrel sp. (white-rumped)"
drop if commonname ==  "white-winged gull sp."

drop if commonname ==  "Anser sp."
drop if commonname ==  "Antrostomus sp."
drop if commonname ==  "Aphelocoma sp."
drop if commonname ==  "Botteri's/Cassin's Sparrow"
drop if commonname ==  "Euplectes sp."
drop if commonname ==  "Glossy/White-faced Ibis"
drop if commonname ==  "Gray-tailed/Wandering Tattler"
drop if commonname ==  "Haemorhous sp."
drop if commonname ==  "Nelson's/Saltmarsh Sparrow (Sharp-tailed Sparrow)"
drop if commonname ==  "Nelson's/Saltmarsh Sparrow (Sharp-tailed)"
drop if commonname ==  "Pacific-slope/Cordilleran Flycatcher"
drop if commonname ==  "Parasitic/Long-tailed Jaeger"
drop if commonname ==  "Scripps's/Guadalupe Murrelet (Xantus' Murrelet)"
drop if commonname ==  "Snow/McKay's Bunting"
drop if commonname ==  "Spotted/Eastern Towhee (Rufous-sided Towhee)"
drop if commonname ==  "flycatcher sp. (Tyrannidae sp.)"

drop if commonname == "Ammodramus sp."
drop if commonname == "Branta sp."
drop if commonname == "Muscovy Duck (Domestic type)"
drop if commonname == "Western/Eastern Wood-Pewee"

***find unique # of species seen in each state in each year
bysort year statecode commonname : gen species_nodup = 1 if _n == 1
bysort year statecode : egen species = total(species_nodup)
label var species "# unique species in state/year"
drop species_nodup

***number of birds seen in each state/year
gen obcount = observationcount if observationcount !="X" //dealing with submissions of "x" birds rather than a number
destring obcount, gen(observations)
label var obcount "string observations"
label var observations "numerical observations"
drop obcount

bysort statecode year: egen birdcount = total(observations)
label var birdcount "number of birds observed in a stateyear"
drop observations

*** creating dependent variable (trail in state/year)
gen birdtrail = 0 //dependent variable (0 = no trail, 1 = has trail)
label var birdtrail "0 = no trail, 1 = has trail"
replace birdtrail = 1 if state == "Alabama" & year >=2000
replace birdtrail = 1 if state == "Alaska" & year >=2009
replace birdtrail = 1 if state == "Arizona" & year >=2006
replace birdtrail = 1 if state == "California" & year >=2003
replace birdtrail = 1 if state == "Colorado" & year >=2000
replace birdtrail = 1 if state == "Delaware" & year >=2007
replace birdtrail = 1 if state == "Florida" & year >=2001
replace birdtrail = 1 if state == "Georgia" & year >=1999
replace birdtrail = 1 if state == "Idaho" & year >=2006
replace birdtrail = 1 if state == "Illinois" & year >=2005
replace birdtrail = 1 if state == "Louisiana" & year >=2002
replace birdtrail = 1 if state == "Maine" & year >=2009
replace birdtrail = 1 if state == "Minnesota" & year >=2007
replace birdtrail = 1 if state == "Mississippi" & year >=2003
replace birdtrail = 1 if state == "Montana" & year >=2004
replace birdtrail = 1 if state == "Nebraska" & year >=2003
replace birdtrail = 1 if state == "New Hampshire" & year >=2002
replace birdtrail = 1 if state == "New Jersey" & year >=2006
replace birdtrail = 1 if state == "New York" & year >=2003
replace birdtrail = 1 if state == "North Carolina" & year >=2004
replace birdtrail = 1 if state == "North Dakota" & year >=2002
replace birdtrail = 1 if state == "Ohio" & year >=2012
replace birdtrail = 1 if state == "Oklahoma" & year >=2006
replace birdtrail = 1 if state == "Oregon" & year >=2003
replace birdtrail = 1 if state == "Pennsylvania" & year >=2005
replace birdtrail = 1 if state == "Rhode Island" & year >=2017
replace birdtrail = 1 if state == "South Dakota" & year >=2003
replace birdtrail = 1 if state == "Texas" & year >=1993
replace birdtrail = 1 if state == "Utah" & year >=2004
replace birdtrail = 1 if state == "Vermont" & year >=2002
replace birdtrail = 1 if state == "Virginia" & year >=2001
replace birdtrail = 1 if state == "Washington" & year >=2002
replace birdtrail = 1 if state == "Wisconsin" & year >=2004


***unique ebirders per state/year (people who input the dataset)
bysort year statecode observerid : gen birders_nodup = 1 if _n == 1
bysort year statecode: egen birders = total(birders_nodup)
label var birders "unique birders by state/year"
drop birders_nodup

***birders per state/year (total # people attached to checklists in dataset)
replace numberobservers = 1 if numberobservers == . //if the value for number of observers is missing then input 1 (there has to be a birder present to input a checklist)

bysort year statecode observerid: egen sumobservers = total(numberobservers) //sum up all the observers attached to checklists submitted by observer(i) in each state/year
bysort year statecode observerid checklist: gen checklists_nodup = 1 if _n == 1
bysort year statecode observerid: egen numberchecklists = total(checklists_nodup) //sum up the number of checklists submitted by observer(i) in each state/year
gen totalbirdergroups = sumobservers - numberchecklists + 1 //only count the checklist submitter once (sum of observers -1 for every checklist he submitted)
bysort year statecode observerid: gen observer_nodup = 1 if _n == 1 // making # of attached birders for a birder = 0 for all but 1st observation for a specific observer/state/year
replace totalbirdergroups = 0 if observer_nodup == . //see previous line (otherwise ALL attached birders are counted every time that lead observer has a checklsit/doubling up numbers)
bysort year statecode: egen totalbirders = total(totalbirdergroups)
label var totalbirders "total number of people attached to checklists by state/year"
drop sumobservers checklists_nodup numberchecklists totalbirdergroups

*birdtrail (not including people's homestate)
gen birdtrail_nohome = birdtrail if homestateyear != statecode
label var birdtrail_nohome ". = state is birder's home state, 0 = no trail, 1 = has trail"

***drop variables that are no longer necessary
drop state observationdate observerid observationcount

save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta", replace


clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta"

***Stakeholder Movement: "exposed population": total number of birders from state x who visited states with birding trails in a year***
*total number of birders from a state each year visiting a state with a BT (excluding home state)
gen dup4 = 0
label var dup4 "first observation in a BT state (home states excluded) for each observer in each year"
bysort observer year birdtrail_nohome: replace dup4 = 1 if _n == 1
replace dup4 = . if birdtrail_nohome == .
replace dup4 = 0 if birdtrail_nohome == 0
bysort homestateyear year: egen N_BTbirders_nohome = total(dup4)
label var N_BTbirders_nohome "total number of birders from a state who visited a state with a birding trail (exclding home states) in each year"
drop dup4

//cut and remerge to realign/match rows from homestateyear to statecode
keep homestateyear year N_BTbirders_nohome
rename homestateyear statecode
bysort statecode year: keep if _n == _N
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument2c_reconstruction.dta", replace

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta"
merge m:1 statecode year using "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument2c_reconstruction.dta"
drop _merge
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta", replace

*using total birders and nohome BT
bysort homestateyear year birdtrail_nohome: egen sumbirders = total(numberobservers) if birdtrail == 1
bysort homestateyear year birdtrail_nohome observer checklist: gen checklist_nodup = 1 if _n == 1
bysort homestateyear year birdtrail_nohome observer: egen numchecklists = total(checklist_nodup)
gen totalhomebirders_nohome = sumbirders - numchecklists + 1
replace totalhomebirders_nohome = . if birdtrail_nohome == 0
replace totalhomebirders_nohome = . if birdtrail_nohome == .
sort homestateyear year totalhomebirders_nohome
bysort homestateyear year: replace totalhomebirders_nohome = totalhomebirders_nohome[_n-1] if totalhomebirders_nohome == .
replace totalhomebirders_nohome = 0 if totalhomebirders_nohome == .
label var totalhomebirders_nohome "total number of birders from state x visiting a birding trail state nohome"
drop sumbirders checklist_nodup numchecklists

//cut and remerge to realign/match rows from homestateyear to statecode
keep homestateyear year totalhomebirders_nohome
rename homestateyear statecode
bysort statecode year: keep if _n == _N
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument2d_reconstruction.dta", replace

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta"
merge m:1 statecode year using "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument2d_reconstruction.dta"
drop _merge
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta", replace


***Alternative Measure 1: "total exposure": total number of visits by birders from state x to states with birding trails in a year***
*using nohome BT
bysort homestateyear year birdtrail_nohome checklist: gen tag2 = 1 if _n == 1
by homestateyear year birdtrail_nohome: egen N_totalstateBTvisits_nohome = total(tag2)
replace N_totalstateBTvisits_nohome = . if birdtrail_nohome == 0 | birdtrail_nohome == .
sort homestateyear year N_totalstateBTvisits_nohome
bysort homestateyear year: replace N_totalstateBTvisits_nohome = N_totalstateBTvisits_nohome[_n-1] if N_totalstateBTvisits_nohome == .
label var N_totalstateBTvisits_nohome "total number of checklists logged in states with birding trails (exclduing home states) by individuals from each state in each year"
drop tag2
replace N_totalstateBTvisits_nohome = 0 if N_totalstateBTvisits_nohome == . //if noone visited they were previously marked as missing

//cut and remerge to realign/match rows from homestateyear to statecode
keep homestateyear year N_totalstateBTvisits_nohome
rename homestateyear statecode
bysort statecode year: keep if _n == _N
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument1c_reconstruction.dta", replace

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta"
merge m:1 statecode year using "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument1c_reconstruction.dta"
drop _merge
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta", replace

*using totalbirders & nohome BT
bysort year homestateyear birdtrail_nohome: egen N_fulltotalstateBTvisits_nohome = total(numberobservers)
replace N_fulltotalstateBTvisits_nohome = . if birdtrail_nohome == 0 | birdtrail_nohome == .
sort homestateyear year N_fulltotalstateBTvisits_nohome
bysort homestateyear year: replace N_fulltotalstateBTvisits_nohome = N_fulltotalstateBTvisits_nohome[_n-1] if N_fulltotalstateBTvisits_nohome == .
label var N_fulltotalstateBTvisits_nohome "total number of checklists (with attached birders) in states with birding trails by individuals from each year_nohome"
replace N_fulltotalstateBTvisits_nohome = 0 if N_fulltotalstateBTvisits_nohome == . //if noone visited they were previously marked as missing

//cut and remerge to realign/match rows from homestateyear to statecode
keep homestateyear year N_fulltotalstateBTvisits_nohome
rename homestateyear statecode
bysort statecode year: keep if _n == _N
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument1d_reconstruction.dta", replace

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta"
merge m:1 statecode year using "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument1d_reconstruction.dta"
drop _merge
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta", replace


***Alternative Measure 2: "percentage population exposed": percentage of birders from state x who visited a state with a birding trail in a year***
*lead birders nohome
drop if birdtrail_nohome == .
gen dup9 = 0
label var dup9 "first observation in a BT state (home states excluded) for each observer in each year"
bysort observer year birdtrail_nohome: replace dup9 = 1 if _n == 1
replace dup9 = . if birdtrail_nohome == .
replace dup9 = 0 if birdtrail_nohome == 0
bysort homestateyear year: egen N_BTbirders_nohome_I4 = total(dup9)
label var N_BTbirders_nohome_I4 "total number of birders from a state who visited a state with a birding trail (exclding home states) in each year"
drop dup9
gen dup10 = 0
label var dup10 "first observation from each birder each year (nohome)"
bysort observer year: replace dup10 = 1 if _n == 1 & birdtrail_nohome != .
bysort homestateyear year: egen N_birders_nohome = total(dup10)
label var N_birders_nohome "total number of birders from a home state in each year (nohome)"
gen percentexposed_nohome = (N_BTbirders_nohome_I4)/(N_birders_nohome)
label var percentexposed_nohome "percentage of lead birders from a home state who visited a state with a birding trail at least once (visits to home state excluded)"
drop dup10

//cut and remerge to realign/match rows from homestateyear to statecode
keep homestateyear year percentexposed_nohome
rename homestateyear statecode
bysort statecode year: keep if _n == _N
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument4c_reconstruction.dta", replace

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta"
merge m:1 statecode year using "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument4c_reconstruction.dta"
drop _merge
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta", replace

*all birders attached to checklists & nohome
drop if birdtrail_nohome == .
bysort homestateyear year birdtrail_nohome: egen sumbirders = total(numberobservers) if birdtrail == 1
bysort homestateyear year birdtrail_nohome observer checklist: gen checklist_nodup = 1 if _n == 1
bysort homestateyear year birdtrail_nohome observer: egen numchecklists = total(checklist_nodup)
gen totalBThomebirders_nohome_I4 = sumbirders - numchecklists + 1
replace totalBThomebirders_nohome_I4 = . if birdtrail_nohome == 0 | birdtrail_nohome == .
sort homestateyear year totalBThomebirders_nohome_I4
bysort homestateyear year: replace totalBThomebirders_nohome_I4 = totalBThomebirders_nohome_I4[_n-1] if totalBThomebirders_nohome_I4 == .
replace totalBThomebirders_nohome_I4 = 0 if totalBThomebirders_nohome_I4 == .
label var totalBThomebirders_nohome_I4 "total number of birders from state x visiting a birding trail state"
drop sumbirders checklist_nodup numchecklists
bysort homestateyear year: egen sumbirders2 = total(numberobservers)
bysort homestateyear year observer checklist: gen checklist_nodup2 = 1 if _n == 1
bysort homestateyear year observer: egen numchecklists2 = total(checklist_nodup2)
gen totalhomebirders_nohome_I4 = sumbirders2 - numchecklists2 + 1
sort homestateyear year totalhomebirders_nohome_I4
bysort homestateyear year: replace totalhomebirders_nohome_I4 = totalhomebirders_nohome_I4[_n-1] if totalhomebirders_nohome_I4 == .
replace totalhomebirders_nohome_I4 = 0 if totalhomebirders_nohome_I4 == .
label var totalhomebirders_nohome_I4 "total number of birders from state x in a year"
drop sumbirders2 checklist_nodup2 numchecklists2
gen full_percentexposed_nohome = (totalBThomebirders_nohome_I4)/(totalhomebirders_nohome_I4)
label var full_percentexposed_nohome "percentage of birders from a home state who visited a state with a birding trail at least once"
drop totalBThomebirders_nohome_I4
drop totalhomebirders_nohome_I4

//cut and remerge to realign/match rows from homestateyear to statecode
keep homestateyear year full_percentexposed_nohome
rename homestateyear statecode
bysort statecode year: keep if _n == _N
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument4d_reconstruction.dta", replace

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta"
merge m:1 statecode year using "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument4d_reconstruction.dta"
drop _merge
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta", replace


***Alternative Measure 3: "total state exposure": total number of states with birding trails visited by birders from state x***
*nohome
gen dup6 = 0
label var dup6 "first observation in each state with a birding trail (excluding home state) from a state in a given year"
bysort homestateyear year birdtrail_nohome statecode: replace dup6 = 1 if _n == 1
replace dup6 = 0 if birdtrail_nohome == 0
replace dup6 = . if birdtrail_nohome == .
bysort homestateyear year: egen N_BTstatesvisited_nohome = total(dup6)
label var N_BTstatesvisited_nohome "total number of states with birding trails (excluding home states) which were visited by birders from a state in a year"
drop dup6

//cut and remerge to realign/match rows from homestateyear to statecode
keep homestateyear year N_BTstatesvisited_nohome
rename homestateyear statecode
bysort statecode year: keep if _n == _N
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument3b_reconstruction.dta", replace

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta"
merge m:1 statecode year using "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\instrument3b_reconstruction.dta"
drop _merge
save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset.dta", replace


***drop duplicates --> unit of observation: state/year
sort statecode year 
bysort statecode year : gen dup = cond(_N==1,0,_n)
drop if dup>1

drop if year<1993
drop if year>2016

***replace missings with 0
replace percentexposed_nohome = 0 if percentexposed_nohome == . //replacing 3 observations as 0 since no birders from BT states visited there
replace full_percentexposed_nohome = 0 if full_percentexposed_nohome == . //replacing 3 observations as 0 since no birders from BT states visited there
replace fullpercentageexposure_nohome = 0 if fullpercentageexposure_nohome == .
replace percentageexposure = 0 if percentageexposure == .
replace fullpercentageexposure = 0 if fullpercentageexposure == .
replace percentageexposure_nohome = 0 if percentageexposure_nohome == .


save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset_uniquestateyear.dta", replace



***Adding other variables
***BIRDING TRAILS***
**Birding trails dataset creation**

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\BT_dataset_uniquestateyear.dta"
gen state = "STATE"
replace state = "Alaska" if statecode == "US-AK"
replace state = "Alabama" if statecode == "US-AL"
replace state = "Arkansas" if statecode == "US-AR"
replace state = "Arizona" if statecode == "US-AZ"
replace state = "California" if statecode == "US-CA"
replace state = "Colorado" if statecode == "US-CO"
replace state = "Connecticut" if statecode == "US-CT"
replace state = "Delaware" if statecode == "US-DE"
replace state = "Florida" if statecode == "US-FL"
replace state = "Georgia" if statecode == "US-GA"
replace state = "Hawaii" if statecode == "US-HI"
replace state = "Iowa" if statecode == "US-IA"
replace state = "Idaho" if statecode == "US-ID"
replace state = "Illinois" if statecode == "US-IL"
replace state = "Indiana" if statecode == "US-IN"
replace state = "Kansas" if statecode == "US-KS"
replace state = "Kentucky" if statecode == "US-KY"
replace state = "Louisiana" if statecode == "US-LA"
replace state = "Massachusetts" if statecode == "US-MA"
replace state = "Maryland" if statecode == "US-MD"
replace state = "Maine" if statecode == "US-ME"
replace state = "Michigan" if statecode == "US-MI"
replace state = "Minnesota" if statecode == "US-MN"
replace state = "Missouri" if statecode == "US-MO"
replace state = "Mississippi" if statecode == "US-MS"
replace state = "Montana" if statecode == "US-MT"
replace state = "North Carolina" if statecode == "US-NC"
replace state = "North Dakota" if statecode == "US-ND"
replace state = "Nebraska" if statecode == "US-NE"
replace state = "New Hampshire" if statecode == "US-NH"
replace state = "New Jersey" if statecode == "US-NJ"
replace state = "New Mexico" if statecode == "US-NM"
replace state = "Nevada" if statecode == "US-NV"
replace state = "New York" if statecode == "US-NY"
replace state = "Ohio" if statecode == "US-OH"
replace state = "Oklahoma" if statecode == "US-OK"
replace state = "Oregon" if statecode == "US-OR"
replace state = "Pennsylvania" if statecode == "US-PA"
replace state = "Rhode Island" if statecode == "US-RI"
replace state = "South Carolina" if statecode == "US-SC"
replace state = "South Dakota" if statecode == "US-SD"
replace state = "Tennessee" if statecode == "US-TN"
replace state = "Texas" if statecode == "US-TX"
replace state = "Utah" if statecode == "US-UT"
replace state = "Virginia" if statecode == "US-VA"
replace state = "Vermont" if statecode == "US-VT"
replace state = "Washington" if statecode == "US-WA"
replace state = "Wisconsin" if statecode == "US-WI"
replace state = "West Virginia" if statecode == "US-WV"
replace state = "Wyoming" if statecode == "US-WY"

gen stcode = .
replace stcode = 1 if statecode == "US-AK"
replace stcode = 2 if statecode == "US-AL"
replace stcode = 3 if statecode == "US-AR"
replace stcode = 4 if statecode == "US-AZ"
replace stcode = 5 if statecode == "US-CA"
replace stcode = 6 if statecode == "US-CO"
replace stcode = 7 if statecode == "US-CT"
replace stcode = 8 if statecode == "US-DE"
replace stcode = 9 if statecode == "US-FL"
replace stcode = 10 if statecode == "US-GA"
replace stcode = 11 if statecode == "US-HI"
replace stcode = 12 if statecode == "US-IA"
replace stcode = 13 if statecode == "US-ID"
replace stcode = 14 if statecode == "US-IL"
replace stcode = 15 if statecode == "US-IN"
replace stcode = 16 if statecode == "US-KS"
replace stcode = 17 if statecode == "US-KY"
replace stcode = 18 if statecode == "US-LA"
replace stcode = 19 if statecode == "US-MA"
replace stcode = 20 if statecode == "US-MD"
replace stcode = 21 if statecode == "US-ME"
replace stcode = 22 if statecode == "US-MI"
replace stcode = 23 if statecode == "US-MN"
replace stcode = 24 if statecode == "US-MO"
replace stcode = 25 if statecode == "US-MS"
replace stcode = 26 if statecode == "US-MT"
replace stcode = 27 if statecode == "US-NC"
replace stcode = 28 if statecode == "US-ND"
replace stcode = 29 if statecode == "US-NE"
replace stcode = 30 if statecode == "US-NH"
replace stcode = 31 if statecode == "US-NJ"
replace stcode = 32 if statecode == "US-NM"
replace stcode = 33 if statecode == "US-NV"
replace stcode = 34 if statecode == "US-NY"
replace stcode = 35 if statecode == "US-OH"
replace stcode = 36 if statecode == "US-OK"
replace stcode = 37 if statecode == "US-OR"
replace stcode = 38 if statecode == "US-PA"
replace stcode = 39 if statecode == "US-RI"
replace stcode = 40 if statecode == "US-SC"
replace stcode = 41 if statecode == "US-SD"
replace stcode = 42 if statecode == "US-TN"
replace stcode = 43 if statecode == "US-TX"
replace stcode = 44 if statecode == "US-UT"
replace stcode = 45 if statecode == "US-VA"
replace stcode = 46 if statecode == "US-VT"
replace stcode = 47 if statecode == "US-WA"
replace stcode = 48 if statecode == "US-WI"
replace stcode = 49 if statecode == "US-WV"
replace stcode = 50 if statecode == "US-WY"

save "\\iac.nas.gatech.edu\emistur3\Papers\As the Crow Flies\Final Dataset\Final_Data.dta", replace

*input population*
gen pop = .

replace pop = 4040587 if state=="Alabama" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop =  4447100 if state=="Alabama" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 4779736 if state=="Alabama" & (year==2010 | year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 550043 if state=="Alaska" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 626932 if state=="Alaska" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 710231 if state=="Alaska" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 3665228 if state=="Arizona" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 5130632 if state=="Arizona" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 6392017 if state=="Arizona" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 2350725 if state=="Arkansas" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 2673400 if state=="Arkansas" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 2915918 if state=="Arkansas" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 29760021 if state=="California" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 33871648 if state=="California" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 37253956 if state=="California" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 3294394 if state=="Colorado" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 4301261 if state=="Colorado" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 5029196 if state=="Colorado" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 3287116 if state=="Connecticut" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 3405565 if state=="Connecticut" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 3574097 if state=="Connecticut" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 666168 if state=="Delaware" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 783600 if state=="Delaware" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 897934 if state=="Delaware" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 12937926 if state=="Florida" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 15982378 if state=="Florida" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 18801310 if state=="Florida" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 6478216 if state=="Georgia" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 8186453 if state=="Georgia" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 9687653 if state=="Georgia" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1108229 if state=="Hawaii" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 1211537 if state=="Hawaii" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 1360301 if state=="Hawaii" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1006749 if state=="Idaho" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 1293953 if state=="Idaho" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 1567582 if state=="Idaho" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 11430602 if state=="Illinois" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 12419293 if state=="Illinois" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 12830632 if state=="Illinois" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 5544159 if state=="Indiana" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 6080485 if state=="Indiana" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 6483802 if state=="Indiana" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 2776755 if state=="Iowa" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 2926324 if state=="Iowa" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 3046355 if state=="Iowa" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 2477574 if state=="Kansas" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 2688418 if state=="Kansas" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 2853118 if state=="Kansas" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 3685296 if state=="Kentucky" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 4041769 if state=="Kentucky" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 4339367 if state=="Kentucky" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 4219973 if state=="Louisiana" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 4468976 if state=="Louisiana" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 4533372 if state=="Louisiana" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1227928 if state=="Maine" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 1274923 if state=="Maine" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 1328361 if state=="Maine" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 4781468 if state=="Maryland" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 5296486 if state=="Maryland" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 5773552 if state=="Maryland" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 6016425 if state=="Massachusetts" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 6349097 if state=="Massachusetts" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 6547629 if state=="Massachusetts" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 9295297 if state=="Michigan" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 9938444 if state=="Michigan" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 9883640 if state=="Michigan" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 4375099 if state=="Minnesota" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 4919479 if state=="Minnesota" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 5303925 if state=="Minnesota" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 2573216 if state=="Mississippi" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 2844658 if state=="Mississippi" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 2967297 if state=="Mississippi" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 5117073 if state=="Missouri" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 5595211 if state=="Missouri" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 5988927 if state=="Missouri" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 799065 if state=="Montana" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 902195 if state=="Montana" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 989415 if state=="Montana" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1578385 if state=="Nebraska" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 1711263 if state=="Nebraska" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 1826341 if state=="Nebraska" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1201833 if state=="Nevada" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 1998257 if state=="Nevada" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 2700551 if state=="Nevada" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1109252 if state=="New Hampshire" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 1235786 if state=="New Hampshire" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 1316470 if state=="New Hampshire" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 7730188 if state=="New Jersey" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 8414350 if state=="New Jersey" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 8791894 if state=="New Jersey" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1515069 if state=="New Mexico" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 1819046 if state=="New Mexico" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 2059179 if state=="New Mexico" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 17990455 if state=="New York" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 18976457 if state=="New York" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 19378102 if state=="New York" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 6628637 if state=="North Carolina" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 8049313 if state=="North Carolina" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 9535483 if state=="North Carolina" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 638800 if state=="North Dakota" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 642200 if state=="North Dakota" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 672591 if state=="North Dakota" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 10847115 if state=="Ohio" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 11353140 if state=="Ohio" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 111536504 if state=="Ohio" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 3145585 if state=="Oklahoma" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 3450654 if state=="Oklahoma" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 3751351 if state=="Oklahoma" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 2842321 if state=="Oregon" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 3421399 if state=="Oregon" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 3831074 if state=="Oregon" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 11881643 if state=="Pennsylvania" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 12281054 if state=="Pennsylvania" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 12702379 if state=="Pennsylvania" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1003464 if state=="Rhode Island" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 1048319 if state=="Rhode Island" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 1052567 if state=="Rhode Island" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 3486703 if state=="South Carolina" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 4012012 if state=="South Carolina" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 4625364 if state=="South Carolina" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 696004 if state=="South Dakota" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 754844 if state=="South Dakota" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 814180 if state=="South Dakota" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 4877185 if state=="Tennessee" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 5689283 if state=="Tennessee" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 6346105 if state=="Tennessee" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 16986510 if state=="Texas" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 20851820 if state=="Texas" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 25145561 if state=="Texas" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1722850 if state=="Utah" & (year==1990 |year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 2233169 if state=="Utah" & (year==2000 |year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 2763885 if state=="Utah" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 562758 if state=="Vermont" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 608827 if state=="Vermont" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 625741 if state=="Vermont" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 6187358 if state=="Virginia" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 7078515 if state=="Virginia" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 8001024 if state=="Virginia" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 4866692 if state=="Washington" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 5894121 if state=="Washington" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 6724540 if state=="Washington" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 1793477 if state=="West Virginia" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 1808344 if state=="West Virginia" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 1852994 if state=="West Virginia" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 4891769 if state=="Wisconsin" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 5363675 if state=="Wisconsin" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 5686986 if state=="Wisconsin" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

replace pop = 453588 if state=="Wyoming" & (year==1990|year==1991|year==1992|year==1993|year==1994|year==1995|year==1996|year==1997|year==1998|year==1999)
replace pop = 493782 if state=="Wyoming" & (year==2000|year==2001|year==2002|year==2003|year==2004|year==2005|year==2006|year==2007|year==2008|year==2009)
replace pop = 563626 if state=="Wyoming" & (year==2010| year==2011 |year==2012 |year==2013 | year==2014 | year ==2015 | year==2016)

label var pop "state population by 10 year census"

save "\\iac.nas.gatech.edu\emistur3\Papers\As the Crow Flies\Final Dataset\Final_Data.dta", replace

*land area by state km^2*
gen area = .
replace area = 135767 if state == "Alabama"
replace area = 1723337 if state == "Alaska"
replace area = 295234 if state == "Arizona"
replace area = 137732 if state == "Arkansas"
replace area = 423967 if state == "California"
replace area = 269601 if state == "Colorado"
replace area = 14357 if state == "Connecticut"
replace area = 6446 if state == "Delaware"
replace area = 170312 if state == "Florida"
replace area = 153910 if state == "Georgia"
replace area = 28313 if state == "Hawaii"
replace area = 216443 if state == "Idaho"
replace area = 149995 if state == "Illinois"
replace area = 94326 if state == "Indiana"
replace area = 145746 if state == "Iowa"
replace area = 213100 if state == "Kansas"
replace area = 104656 if state == "Kentucky"
replace area = 135659 if state == "Louisiana"
replace area = 91633 if state == "Maine"
replace area = 32131 if state == "Maryland"
replace area = 27336 if state == "Massachusetts"
replace area = 250487 if state == "Michigan"
replace area = 225163 if state == "Minnesota"
replace area = 125438 if state == "Mississippi"
replace area = 180540 if state == "Missouri"
replace area = 380831 if state == "Montana"
replace area = 200330 if state == "Nebraska"
replace area = 286380 if state == "Nevada"
replace area = 24214 if state == "New Hampshire"
replace area = 22591 if state == "New Jersey"
replace area = 314917 if state == "New Mexico"
replace area = 141297 if state == "New York"
replace area = 139391 if state == "North Carolina"
replace area = 183108 if state == "North Dakota"
replace area = 116098 if state == "Ohio"
replace area = 181037 if state == "Oklahoma"
replace area = 254799 if state == "Oregon"
replace area = 119280 if state == "Pennsylvania"
replace area = 4001 if state == "Rhode Island"
replace area = 82933 if state == "South Carolina"
replace area = 199729 if state == "South Dakota"
replace area = 109153 if state == "Tennessee"
replace area = 695662 if state == "Texas"
replace area = 219882 if state == "Utah"
replace area = 24906 if state == "Vermont"
replace area = 110787 if state == "Virginia"
replace area = 184661 if state == "Washington"
replace area = 62756 if state == "West Virginia"
replace area = 169635 if state == "Wisconsin"
replace area = 253335 if state == "Wyoming"

label var area "state land area Km^2"
save "\\iac.nas.gatech.edu\emistur3\Papers\As the Crow Flies\Final Dataset\Final_Data.dta", replace

gen totaltrails = .
label var totaltrails "total number of other trails"
replace totaltrails = 1 if year==1993 | year==1994 | year==1995 | year==1996 | year==1997 | year==1998
replace totaltrails = 0 if state == "Texas" & (year == 1993 | year==1994 | year==1995 | year==1996 | year==1997 | year==1998)
replace totaltrails = 2 if year==1999
replace totaltrails = 1 if state == "Georgia" & year==1999 
replace totaltrails = 4 if year == 2000
replace totaltrails = 3 if state == "Colorado" & year==2000
replace totaltrails = 3 if state == "Alabama" & year==2000
replace totaltrails = 6 if year == 2001
replace totaltrails = 5 if (state == "Virginia" | state == "Florida") & year == 2001
replace totaltrails = 10 if year == 2002
replace totaltrails = 9 if (state == "Vermont" | state == "North Dakota" | state == "New Hampshire" | state == "Louisiana") &  year ==2002
replace totaltrails = 16 if year == 2003
replace totaltrails = 15 if (state == "South Dakota" | state == "New York" | state == "Nebraska" | state == "Mississippi" | state == "California" | state == "Oregon") & year == 2003
replace totaltrails = 20 if year == 2004
replace totaltrails = 19 if (state == "Wisconsin" | state == "Utah" | state == "North Carolina" | state == "Montana") & year==2004
replace totaltrails = 22 if year == 2005
replace totaltrails = 21 if (state == "Pennsylvania" | state == "Illinois") & year == 2005
replace totaltrails = 27 if year == 2006
replace totaltrails = 26 if (state == "Arizona" | state == "Washington" | state == "Oklahoma" | state == "New Jersey" |  state == "Idaho") & year == 2006
replace totaltrails = 29 if year == 2007 | year == 2008
replace totaltrails = 28 if (state == "Minnesota" | state == "Delaware") & (year == 2007 | year == 2008)
replace totaltrails = 31 if year == 2009 | year == 2010 | year == 2011
replace totaltrails = 30 if (state == "Maine" | state == "Alaska") & (year == 2009 | year == 2010 | year == 2011)
replace totaltrails = 32 if year == 2012 | year == 2013 | year == 2014 | year == 2015
replace totaltrails = 31 if state == "Ohio" & (year == 2012 | year == 2013 | year == 2014 | year == 2015)


*Add in control variables for US tourism spending & imports*
gen CPI = .
replace CPI = 144.5 if year == 1993
replace CPI = 148.2 if year == 1994
replace CPI = 152.4 if year == 1995
replace CPI = 156.9 if year == 1996
replace CPI = 160.5 if year == 1997
replace CPI = 163.0 if year == 1998
replace CPI = 166.6 if year == 1999
replace CPI = 172.2 if year == 2000
replace CPI = 177.1 if year == 2001
replace CPI = 179.9 if year == 2002
replace CPI = 184.0 if year == 2003
replace CPI = 188.9 if year == 2004
replace CPI = 195.3 if year == 2005
replace CPI = 201.6 if year == 2006
replace CPI = 207.3 if year == 2007
replace CPI = 215.303 if year == 2008
replace CPI = 214.537 if year == 2009
replace CPI = 218.056 if year == 2010
replace CPI = 224.939 if year == 2011
replace CPI = 229.594 if year == 2012
replace CPI = 232.957 if year == 2013
replace CPI = 236.736 if year == 2014
replace CPI = 237.017 if year == 2015
replace CPI = 240.007 if year == 2016
replace CPI = 245.120 if year == 2017

gen spend = .
label var spend "annual US tourism spending (goods & services in 2013 $)"
replace spend = 74403*(232.957/CPI) if year == 1993
replace spend = 75414*(232.957/CPI) if year == 1994
replace spend = 82304*(232.957/CPI) if year == 1995
replace spend = 90231*(232.957/CPI) if year == 1996
replace spend = 94294*(232.957/CPI) if year == 1997
replace spend = 91234*(232.957/CPI) if year == 1998
replace spend = 94875*(232.957/CPI) if year == 1999
replace spend = 102560*(232.957/CPI) if year == 2000
replace spend = 84630*(232.957/CPI) if year == 2001
replace spend = 77380*(232.957/CPI) if year == 2002
replace spend = 73779*(232.957/CPI) if year == 2003
replace spend = 87633*(232.957/CPI) if year == 2004
replace spend = 97609*(232.957/CPI) if year == 2005
replace spend = 101210*(232.957/CPI) if year == 2006
replace spend = 117919*(232.957/CPI) if year == 2007
replace spend = 135577*(232.957/CPI) if year == 2008
replace spend = 116782*(232.957/CPI) if year == 2009
replace spend = 137840*(232.957/CPI) if year == 2010
replace spend = 155408*(232.957/CPI) if year == 2011
replace spend = 166109*(232.957/CPI) if year == 2012
replace spend = 180465*(232.957/CPI) if year == 2013
replace spend = 193308*(232.957/CPI) if year == 2014
replace spend = 198686*(232.957/CPI) if year == 2015
replace spend = 192820*(232.957/CPI) if year == 2016

gen imports = .
label var imports "annual US tourism imports (goods & services in 2013 $)"
replace imports = 52123*(232.957/CPI) if year == 1993
replace imports = 56844*(232.957/CPI) if year == 1994
replace imports = 59579*(232.957/CPI) if year == 1995
replace imports = 63887*(232.957/CPI) if year == 1996
replace imports = 70189*(232.957/CPI) if year == 1997
replace imports = 76454*(232.957/CPI) if year == 1998
replace imports = 75429*(232.957/CPI) if year == 1999
replace imports = 83631*(232.957/CPI) if year == 2000
replace imports = 76816*(232.957/CPI) if year == 2001
replace imports = 73203*(232.957/CPI) if year == 2002
replace imports = 75310*(232.957/CPI) if year == 2003
replace imports = 89690*(232.957/CPI) if year == 2004
replace imports = 96290*(232.957/CPI) if year == 2005
replace imports = 100977*(232.957/CPI) if year == 2006
replace imports = 106494*(232.957/CPI) if year == 2007
replace imports = 112956*(232.957/CPI) if year == 2008
replace imports = 95917*(232.957/CPI) if year == 2009
replace imports = 102497*(232.957/CPI) if year == 2010
replace imports = 108409*(232.957/CPI) if year == 2011
replace imports = 121353*(232.957/CPI) if year == 2012
replace imports = 121009*(232.957/CPI) if year == 2013
replace imports = 130554*(232.957/CPI) if year == 2014
replace imports = 137945*(232.957/CPI) if year == 2015
replace imports = 147885*(232.957/CPI) if year == 2016

*new economic control data
merge 1:1 stcode year using "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\Final_Code\Economic Data\State_travel_econ_data_1999_2016.dta"
rename y travelspending
label var travelspending "annual state travel & tourism expenditures (2013 $)"
drop U statename units _merge

replace travelspending = travelspending*(232.957/CPI) if year == 1993
replace travelspending = travelspending*(232.957/CPI) if year == 1994
replace travelspending = travelspending*(232.957/CPI) if year == 1995
replace travelspending = travelspending*(232.957/CPI) if year == 1996
replace travelspending = travelspending*(232.957/CPI) if year == 1997
replace travelspending = travelspending*(232.957/CPI) if year == 1998
replace travelspending = travelspending*(232.957/CPI) if year == 1999
replace travelspending = travelspending*(232.957/CPI) if year == 2000
replace travelspending = travelspending*(232.957/CPI) if year == 2001
replace travelspending = travelspending*(232.957/CPI) if year == 2002
replace travelspending = travelspending*(232.957/CPI) if year == 2003
replace travelspending = travelspending*(232.957/CPI) if year == 2004
replace travelspending = travelspending*(232.957/CPI) if year == 2005
replace travelspending = travelspending*(232.957/CPI) if year == 2006
replace travelspending = travelspending*(232.957/CPI) if year == 2007
replace travelspending = travelspending*(232.957/CPI) if year == 2008
replace travelspending = travelspending*(232.957/CPI) if year == 2009
replace travelspending = travelspending*(232.957/CPI) if year == 2010
replace travelspending = travelspending*(232.957/CPI) if year == 2011
replace travelspending = travelspending*(232.957/CPI) if year == 2012
replace travelspending = travelspending*(232.957/CPI) if year == 2013
replace travelspending = travelspending*(232.957/CPI) if year == 2014
replace travelspending = travelspending*(232.957/CPI) if year == 2015
replace travelspending = travelspending*(232.957/CPI) if year == 2016


*create diffusion variables*
gen adjstate = .
label var adjstate "number of adjacent states with birding trails"
replace adjstate = 0 if state == "Wyoming" & (year == 1993 | year == 1994 | year == 1995 | year == 1996 |year==1997|year==1998|year==1999)
replace adjstate = 1 if state == "Wyoming" & (year==2000|year==2001|year==2002)
replace adjstate = 3 if state == "Wyoming" & year==2003
replace adjstate = 5 if state == "Wyoming" & (year==2004|year==2005)
replace adjstate = 6 if state == "Wyoming" & inrange(year,2006,2016)

replace adjstate = 0 if state == "West Virginia" & inrange(year,1993,2000)
replace adjstate = 1 if state == "West Virginia" & inrange(year,2001,2004)
replace adjstate = 2 if state == "West Virginia" & inrange(year,2005,2011)
replace adjstate = 3 if state == "West Virginia" & inrange(year,2012,2016)

replace adjstate = 0 if state == "Tennessee" & inrange(year,1993,1998)
replace adjstate = 1 if state == "Tennessee" & year==1999
replace adjstate = 2 if state == "Tennessee" & year==2000
replace adjstate = 3 if state == "Tennessee" & inrange(year,2001,2002)
replace adjstate = 4 if state == "Tennessee" & year==2003
replace adjstate = 5 if state == "Tennessee" & inrange(year,2004,2016)

replace adjstate = 0 if state == "South Carolina" & inrange(year,1993,1998)
replace adjstate = 1 if state == "South Carolina" & inrange(year,1999,2003)
replace adjstate = 2 if state == "South Carolina" & inrange(year,2004,2016)

replace adjstate = 0 if state == "Rhode Island"

replace adjstate = 0 if state == "Nevada" & inrange(year,1993,2002)
replace adjstate = 2 if state == "Nevada" & year==2003
replace adjstate = 3 if state == "Nevada" & inrange(year,2004,2005)
replace adjstate = 5 if state == "Nevada" & inrange(year,2006,2016)

replace adjstate = 1 if state == "New Mexico" & inrange(year,1993,1999)
replace adjstate = 2 if state == "New Mexico" & inrange(year,2000,2003)
replace adjstate = 3 if state == "New Mexico" & inrange(year,2004,2005)
replace adjstate = 5 if state == "New Mexico" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Missouri" & inrange(year,1993,2002)
replace adjstate = 1 if state == "Missouri" & inrange(year,2003,2004)
replace adjstate = 2 if state == "Missouri" & year==2005
replace adjstate = 3 if state == "Missouri" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Michigan" & inrange(year,1993,2003)
replace adjstate = 1 if state == "Michigan" & inrange(year,2004,2011)
replace adjstate = 2 if state == "Michigan" & inrange(year,2012,2016)

replace adjstate = 0 if state == "Maine" & inrange(year,1993,2001)
replace adjstate = 1 if state == "Maine" & inrange(year,2002,2016)

replace adjstate = 0 if state == "Maryland" & inrange(year,1993,2000)
replace adjstate = 1 if state == "Maryland" & inrange(year,2001,2004)
replace adjstate = 2 if state == "Maryland" & inrange(year,2005,2006)
replace adjstate = 3 if state == "Maryland" & inrange(year,2007,2016)

replace adjstate = 0 if state == "Massachusetts" & inrange(year,1993,2001)
replace adjstate = 2 if state == "Massachusetts" & year==2002
replace adjstate = 3 if state == "Massachusetts" & inrange(year,2003,2016)
 
replace adjstate = 0 if state == "Kentucky" & inrange(year,1993,2000)
replace adjstate = 1 if state == "Kentucky" & inrange(year,2001,2004)
replace adjstate = 2 if state == "Kentucky" & inrange(year,2005,2011)
replace adjstate = 3 if state == "Kentucky" & inrange(year,2012,2016)

replace adjstate = 0 if state == "Kansas" & inrange(year,1993,1999)
replace adjstate = 1 if state == "Kansas" & inrange(year,2000,2002)
replace adjstate = 2 if state == "Kansas" & inrange(year,2003,2005)
replace adjstate = 3 if state == "Kansas" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Indiana" & inrange(year,1993,2004)
replace adjstate = 1 if state == "Indiana" & inrange(year,2005,2011)
replace adjstate = 2 if state == "Indiana" & inrange(year,2012,2016)

replace adjstate = 0 if state == "Idaho" & inrange(year,1993,2002)
replace adjstate = 1 if state == "Idaho" & year==2003
replace adjstate = 3 if state == "Idaho" & inrange(year,2004,2005)
replace adjstate = 4 if state == "Idaho" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Hawaii"

replace adjstate = 0 if state == "Connecticut" & inrange(year,1993,2002)
replace adjstate = 1 if state == "Connecticut" & inrange(year,2003,2016)

replace adjstate = 1 if state == "Arkansas" & inrange(year,1993,2001)
replace adjstate = 2 if state == "Arkansas" & year==2002
replace adjstate = 3 if state == "Arkansas" & inrange(year,2003,2005)
replace adjstate = 4 if state == "Arkansas" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Ohio" & inrange(year,1993,2004)
replace adjstate = 1 if state == "Ohio" & inrange(year,2005,2016)

replace adjstate = 0 if state == "Alaska"

replace adjstate = 0 if state == "Maine" & inrange(year,1993,2001)
replace adjstate = 1 if state == "Maine" & inrange(year,2002,2016)

replace adjstate = 0 if state == "Delaware" & inrange(year,1993,2004)
replace adjstate = 1 if state == "Delaware" & year==2005
replace adjstate = 2 if state == "Delaware" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Minnesota" & inrange(year,1993,2002)
replace adjstate = 1 if state == "Minnesota" & year==2003
replace adjstate = 3 if state == "Minnesota" & inrange(year,2004,2016)

replace adjstate = 0 if state == "New Jersey" & inrange(year,1993,2002)
replace adjstate = 1 if state == "New Jersey" & inrange(year,2003,2004)
replace adjstate = 2 if state == "New Jersey" & inrange(year,2005,2006)
replace adjstate = 3 if state == "New Jersey" & inrange(year,2007,2016)

replace adjstate = 1 if state == "Oklahoma" & inrange(year,1993,1999)
replace adjstate = 2 if state == "Oklahoma" & inrange(year,2000,2016)

replace adjstate = 0 if state == "Washington" & inrange(year,1993,2002)
replace adjstate = 1 if state == "Washington" & inrange(year,2003,2005)
replace adjstate = 2 if state == "Washington" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Arizona" & inrange(year,1993,1999)
replace adjstate = 1 if state == "Arizona" & inrange(year,2000,2002)
replace adjstate = 2 if state == "Arizona" & year ==2003
replace adjstate = 3 if state == "Arizona" & inrange(year,2004,2016)

replace adjstate = 0 if state == "Illinois" & inrange(year,1993,2003)
replace adjstate = 1 if state == "Illinois" & inrange(year,2003,2016)

replace adjstate = 0 if state == "Pennsylvania" & inrange(year,1993,2002)
replace adjstate = 1 if state == "Pennsylvania" & inrange(year,2003,2005)
replace adjstate = 2 if state == "Pennsylvania" & year==2006
replace adjstate = 3 if state == "Pennsylvania" & inrange(year,2007,2011)
replace adjstate = 4 if state == "Pennsylvania" & inrange(year,2012,2016)

replace adjstate = 0 if state == "Montana" & inrange(year,1993,2001)
replace adjstate = 1 if state == "Montana" & year==2002
replace adjstate = 2 if state == "Montana" & inrange(year,2003,2005)
replace adjstate = 3 if state == "Montana" & inrange(year,2006,2016)

replace adjstate = 0 if state == "North Carolina" & inrange(year,1993,1998)
replace adjstate = 1 if state == "North Carolina" & inrange(year,1999,2000)
replace adjstate = 2 if state == "North Carolina" & inrange(year,2001,2016)

replace adjstate = 0 if state == "Utah" & inrange(year,1993,1999)
replace adjstate = 1 if state == "Utah" & inrange(year,2000,2005)
replace adjstate = 3 if state == "Utah" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Wisconsin" & inrange(year,1993,2004)
replace adjstate = 1 if state == "Wisconsin" & inrange(year,2005,2006)
replace adjstate = 2 if state == "Wisconsin" & inrange(year,2007,2016)

replace adjstate = 0 if state == "Oregon" & inrange(year,1993,2002)
replace adjstate = 1 if state == "Oregon" & inrange(year,2003,2005)
replace adjstate = 3 if state == "Oregon" & inrange(year,2006,2016)

replace adjstate = 0 if state == "California" & inrange(year,1993,2002)
replace adjstate = 1 if state == "California" & inrange(year,2003,2005)
replace adjstate = 2 if state == "California" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Mississippi" & inrange(year,1993,1999)
replace adjstate = 1 if state == "Mississippi" & inrange(year,2000,2009)
replace adjstate = 2 if state == "Mississippi" & inrange(year,2002,2016)

replace adjstate = 0 if state == "Nebraska" & inrange(year,1993,1999)
replace adjstate = 1 if state == "Nebraska" & inrange(year,2000,2002)
replace adjstate = 2 if state == "Nebraska" & year==2003
replace adjstate = 3 if state == "Nebraska" & inrange(year,2004,2016)

replace adjstate = 0 if state == "New York" & inrange(year,1993,2001)
replace adjstate = 1 if state == "New York" & inrange(year,2002,2004)
replace adjstate = 2 if state == "New York" & year==2005
replace adjstate = 3 if state == "New York" & inrange(year,2006,2016)

replace adjstate = 0 if state == "South Dakota" & inrange(year,1993,2001)
replace adjstate = 1 if state == "South Dakota" & year==2002
replace adjstate = 2 if state == "South Dakota" & year==2003
replace adjstate = 3 if state == "South Dakota" & inrange(year,2004,2006)
replace adjstate = 4 if state == "South Dakota" & inrange(year,2007,2016)

replace adjstate = 0 if state == "Louisiana"
replace adjstate = 1 if state == "Louisiana" & inrange(year,1993,2016)

replace adjstate = 0 if state == "New Hampshire" & inrange(year,1993,2001)
replace adjstate = 1 if state == "New Hampshire" & inrange(year,2002,2008)
replace adjstate = 2 if state == "New Hampshire" & inrange(year,2009,2016)

replace adjstate = 0 if state == "North Dakota" & inrange(year,1993,2002)
replace adjstate = 1 if state == "North Dakota" & year==2003
replace adjstate = 2 if state == "North Dakota" & inrange(year,2004,2006)
replace adjstate = 3 if state == "North Dakota" & inrange(year,2007,2016)

replace adjstate = 0 if state == "Vermont" & inrange(year,1993,2001)
replace adjstate = 1 if state == "Vermont" & year==2002
replace adjstate = 2 if state == "Vermont" & inrange(year,2003,2016)

replace adjstate = 0 if state == "Florida" & inrange(year,1993,1998)
replace adjstate = 1 if state == "Florida" & year==1999
replace adjstate = 2 if state == "Florida" & inrange(year,2000,2016)

replace adjstate = 0 if state == "Virginia" & inrange(year,1993,2003)
replace adjstate = 1 if state == "Virginia" & inrange(year,2004,2016)

replace adjstate = 0 if state == "Alabama" & inrange(year,1993,1998)
replace adjstate = 1 if state == "Alabama" & inrange(year,1999,2000)
replace adjstate = 2 if state == "Alabama" & inrange(year,2001,2002)
replace adjstate = 3 if state == "Alabama" & inrange(year,2003,2016)

replace adjstate = 0 if state == "Colorado" & inrange(year,1993,2002)
replace adjstate = 1 if state == "Colorado" & year==2003
replace adjstate = 2 if state == "Colorado" & inrange(year,2004,2005)
replace adjstate = 4 if state == "Colorado" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Georgia" & inrange(year,1993,1999)
replace adjstate = 1 if state == "Georgia" & year==2000
replace adjstate = 2 if state == "Georgia" & inrange(year,2001,2003)
replace adjstate = 3 if state == "Georgia" & year==2004
replace adjstate = 3 if state == "Georgia" & inrange(year,2005,2016)

replace adjstate = 0 if state == "Texas" & inrange(year,1993,2001)
replace adjstate = 1 if state == "Texas" & inrange(year,2002,2005)
replace adjstate = 2 if state == "Texas" & year==2006
replace adjstate = 2 if state == "Texas" & inrange(year,2006,2016)

replace adjstate = 0 if state == "Iowa" & inrange(year,1993,2002)
replace adjstate = 2 if state == "Iowa" & year==2003
replace adjstate = 4 if state == "Iowa" & year==2004
replace adjstate = 5 if state == "Iowa" & inrange(year,2005,2006)
replace adjstate = 6 if state == "Iowa" & inrange(year,2007,2016)

gen adjacentstates=.
label var adjacentstates "number of adjacent states"
replace adjacentstates = 0 if state=="Alaska"
replace adjacentstates = 4 if state=="Alabama"
replace adjacentstates = 6 if state=="Arkansas"
replace adjacentstates = 5 if state=="Arizona"
replace adjacentstates = 3 if state=="California"
replace adjacentstates = 7 if state=="Colorado"
replace adjacentstates = 3 if state=="Connecticut"
replace adjacentstates = 3 if state=="Delaware"
replace adjacentstates = 2 if state=="Florida"
replace adjacentstates = 5 if state=="Georgia"
replace adjacentstates = 0 if state=="Hawaii"
replace adjacentstates = 6 if state=="Iowa"
replace adjacentstates = 6 if state=="Idaho"
replace adjacentstates = 5 if state=="Illinois"
replace adjacentstates = 4 if state=="Indiana"
replace adjacentstates = 4 if state=="Kansas"
replace adjacentstates = 7 if state=="Kentucky"
replace adjacentstates = 3 if state=="Louisiana"
replace adjacentstates = 5 if state=="Massachusetts"
replace adjacentstates = 4 if state=="Maryland"
replace adjacentstates = 1 if state=="Maine"
replace adjacentstates = 3 if state=="Michigan"
replace adjacentstates = 4 if state=="Minnesota"
replace adjacentstates = 8 if state=="Missouri"
replace adjacentstates = 4 if state=="Mississippi"
replace adjacentstates = 4 if state=="Montana"
replace adjacentstates = 4 if state=="North Carolina"
replace adjacentstates = 3 if state=="North Dakota"
replace adjacentstates = 6 if state=="Nebraska"
replace adjacentstates = 3 if state=="New Hampshire"
replace adjacentstates = 3 if state=="New Jersey"
replace adjacentstates = 5 if state=="New Mexico"
replace adjacentstates = 5 if state=="Nevada"
replace adjacentstates = 5 if state=="New York"
replace adjacentstates = 5 if state=="Ohio"
replace adjacentstates = 6 if state=="Oklahoma"
replace adjacentstates = 4 if state=="Oregon"
replace adjacentstates = 6 if state=="Pennsylvania"
replace adjacentstates = 2 if state=="Rhode Island"
replace adjacentstates = 2 if state=="South Carolina"
replace adjacentstates = 6 if state=="South Dakota"
replace adjacentstates = 8 if state=="Tennessee"
replace adjacentstates = 4 if state=="Texas"
replace adjacentstates = 6 if state=="Utah"
replace adjacentstates = 5 if state=="Virginia"
replace adjacentstates = 3 if state=="Vermont"
replace adjacentstates = 2 if state=="Washington"
replace adjacentstates = 4 if state=="Wisconsin"
replace adjacentstates = 5 if state=="West Virginia"
replace adjacentstates = 6 if state=="Wyoming"

gen percentadjstate = .
label var percentadjstate "percent of adjacent states with birding trails"
replace percentadjstate = ((adjstate/adjacentstates)*100)

save "\\iac.nas.gatech.edu\emistur3\Papers\As the Crow Flies\Final Dataset\Final_Data.dta", replace


clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\state ideology.dta"  //state ideology data
drop state
gen statecode = "STATE"
replace statecode = "US-AK" if statename == "Alaska"
replace statecode = "US-AL" if statename == "Alabama"
replace statecode = "US-AR" if statename == "Arkansas"
replace statecode = "US-AZ" if statename == "Arizona"
replace statecode = "US-CA" if statename == "California"
replace statecode = "US-CO" if statename == "Colorado"
replace statecode = "US-CT" if statename == "Connecticut"
replace statecode = "US-DE" if statename == "Delaware"
replace statecode = "US-FL" if statename == "Florida"
replace statecode = "US-GA" if statename == "Georgia"
replace statecode = "US-HI" if statename == "Hawaii"
replace statecode = "US-IA" if statename == "Iowa"
replace statecode = "US-ID" if statename == "Idaho"
replace statecode = "US-IL" if statename == "Illinios"
replace statecode = "US-IN" if statename == "Indiana"
replace statecode = "US-KS" if statename == "Kansas"
replace statecode = "US-KY" if statename == "Kentucky"
replace statecode = "US-LA" if statename == "Louisiana"
replace statecode = "US-MA" if statename == "Massachusetts"
replace statecode = "US-MD" if statename == "Maryland"
replace statecode = "US-ME" if statename == "Maine"
replace statecode = "US-MI" if statename == "Michigan"
replace statecode = "US-MN" if statename == "Minnesota"
replace statecode = "US-MO" if statename == "Missouri"
replace statecode = "US-MS" if statename == "Mississippi"
replace statecode = "US-MT" if statename == "Montana"
replace statecode = "US-NC" if statename == "North Carolina"
replace statecode = "US-ND" if statename == "North Dakota"
replace statecode = "US-NE" if statename == "Nebraska"
replace statecode = "US-NH" if statename == "New Hampshire"
replace statecode = "US-NJ" if statename == "New Jersey"
replace statecode = "US-NM" if statename == "New Mexico"
replace statecode = "US-NV" if statename == "Nevada"
replace statecode = "US-NY" if statename == "New York"
replace statecode = "US-OH" if statename == "Ohio"
replace statecode = "US-OK" if statename == "Oklahoma"
replace statecode = "US-OR" if statename == "Oregon"
replace statecode = "US-PA" if statename == "Pennsylvania"
replace statecode = "US-RI" if statename == "Rhode Island"
replace statecode = "US-SC" if statename == "South Carolina"
replace statecode = "US-SD" if statename == "South Dakota"
replace statecode = "US-TN" if statename == "Tennessee"
replace statecode = "US-TX" if statename == "Texas"
replace statecode = "US-UT" if statename == "Utah"
replace statecode = "US-VA" if statename == "Virginia"
replace statecode = "US-VT" if statename == "Vermont"
replace statecode = "US-WA" if statename == "Washington"
replace statecode = "US-WI" if statename == "Wisconsin"
replace statecode = "US-WV" if statename == "West Virginia"
replace statecode = "US-WY" if statename == "Wyoming"

drop if year < 1993 //first trail in 1993 (no need for data before then)

save "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\state_ideology_2018.dta", replace  //state ideology data

clear
set mo off
use "\\iac.nas.gatech.edu\emistur3\Papers\As the Crow Flies\Final Dataset\Final_Data.dta"
drop if statecode == "US-DC"
merge 1:1 statecode year using "\\iac.nas.gatech.edu\emistur3\EM_Dissertation\Birding_Trails\Data\2018\state_ideology_2018.dta"

save "\\iac.nas.gatech.edu\emistur3\Papers\As the Crow Flies\Final Dataset\Final_Data.dta", replace

*bird density variable
gen birddensity = birdcount/birders
label var birddensity "number of birds seen per birder in state/year"

drop if state =="Alaska" | state=="Hawaii"
drop if state =="District of Columbia"
drop commonname localityid timeobservationsstarted numberobservers observer date checklist homestateyear dup state CPI adjacentstates statename _merge

save "\\iac.nas.gatech.edu\emistur3\Papers\As the Crow Flies\Final Dataset\Final_Data.dta", replace

//generating annual dummies
gen year1993 = 0
replace year1993 = 1 if year == 1993
gen year1994 = 0
replace year1994 = 1 if year == 1994
gen year1995 = 0
replace year1995 = 1 if year == 1995
gen year1996 = 0
replace year1996 = 1 if year == 1996
gen year1997 = 0
replace year1997 = 1 if year == 1997
gen year1998 = 0
replace year1998 = 1 if year == 1998
gen year1999 = 0
replace year1999 = 1 if year == 1999
gen year2000 = 0
replace year2000 = 1 if year == 2000
gen year2001 = 0
replace year2001 = 1 if year == 2001
gen year2002 = 0
replace year2002 = 1 if year == 2002
gen year2003 = 0
replace year2003 = 1 if year == 2003
gen year2004 = 0
replace year2004 = 1 if year == 2004
gen year2005 = 0
replace year2005 = 1 if year == 2005
gen year2006 = 0
replace year2006 = 1 if year == 2006
gen year2007 = 0
replace year2007 = 1 if year == 2007
gen year2008 = 0
replace year2008 = 1 if year == 2008
gen year2009 = 0
replace year2009 = 1 if year == 2009
gen year2010 = 0
replace year2010 = 1 if year == 2010
gen year2011 = 0
replace year2011 = 1 if year == 2011
gen year2012 = 0
replace year2012 = 1 if year == 2012
gen year2013 = 0
replace year2013 = 1 if year == 2013
gen year2014 = 0
replace year2014 = 1 if year == 2014
gen year2015 = 0
replace year2015 = 1 if year == 2015
gen year2016 = 0
replace year2016 = 1 if year == 2016

gen yeardummy = 0
replace yeardummy = 1 if year1994 == 1
replace yeardummy = 2 if year1995 == 1
replace yeardummy = 3 if year1996 == 1
replace yeardummy = 4 if year1997 == 1
replace yeardummy = 5 if year1998 == 1
replace yeardummy = 6 if year1999 == 1
replace yeardummy = 7 if year2000 == 1
replace yeardummy = 8 if year2001 == 1
replace yeardummy = 9 if year2002 == 1
replace yeardummy = 10 if year2003 == 1
replace yeardummy = 11 if year2004 == 1
replace yeardummy = 12 if year2005 == 1
replace yeardummy = 13 if year2006 == 1
replace yeardummy = 14 if year2007 == 1
replace yeardummy = 15 if year2008 == 1
replace yeardummy = 16 if year2009 == 1
replace yeardummy = 17 if year2010 == 1
replace yeardummy = 18 if year2011 == 1
replace yeardummy = 19 if year2012 == 1
replace yeardummy = 20 if year2013 == 1
replace yeardummy = 21 if year2014 == 1
replace yeardummy = 22 if year2015 == 1
replace yeardummy = 23 if year2016 == 1
label var yeardummy "set of yearly dummies"

drop if statecode == "STATE"



*lagging variables (lagged by 1 year)
sort statecode year
by statecode: gen spend_lag = spend[_n-1]
by statecode: gen imports_lag = imports[_n-1]
by statecode: gen species_lag = species[_n-1]
by statecode: gen birdcount_lag = birdcount[_n-1]
by statecode: gen birders_lag = birders[_n-1]
by statecode: gen birddensity_lag = birddensity[_n-1]
by statecode: gen totalbirders_lag = totalbirders[_n-1]
by statecode: gen pop_lag = pop[_n-1]
by statecode: gen citi6013_lag = citi6013[_n-1]
by statecode: gen inst6013_adacope_lag = inst6013_adacope[_n-1]
by statecode: gen inst6014_nom_lag = inst6014_nom[_n-1]
by statecode: gen totaltrails_lag = totaltrails[_n-1]
by statecode: gen percentadjstate_lag = percentadjstate[_n-1]

by statecode: gen N_totalstateBTvisits_lag = N_totalstateBTvisits[_n-1]
by statecode: gen N_fulltotalstateBTvisits_lag = N_fulltotalstateBTvisits[_n-1]
by statecode: gen N_totalstateBTvisits_nohome_lag = N_totalstateBTvisits_nohome[_n-1]
by statecode: gen N_fulltotalstateBTvisits_nh_lag = N_fulltotalstateBTvisits_nohome[_n-1]
by statecode: gen N_BTbirders_lag = N_BTbirders[_n-1]
by statecode: gen N_BTbirders_nohome_lag = N_BTbirders_nohome[_n-1]
by statecode: gen totalhomebirders_lag = totalhomebirders[_n-1]
by statecode: gen totalhomebirders_nohome_lag = totalhomebirders_nohome[_n-1]
by statecode: gen N_BTstatesvisited_lag = N_BTstatesvisited[_n-1]
by statecode: gen N_BTstatesvisited_nohome_lag = N_BTstatesvisited_nohome[_n-1]
by statecode: gen percentexposed_lag = percentexposed[_n-1]
by statecode: gen full_percentexposed_lag = full_percentexposed[_n-1]
by statecode: gen percentexposed_nohome_lag = percentexposed_nohome[_n-1]
by statecode: gen full_percentexposed_nohome_lag = full_percentexposed_nohome[_n-1]
by statecode: gen percentageexposure_lag = percentageexposure[_n-1]
by statecode: gen fullpercentageexposure_lag = fullpercentageexposure[_n-1]
by statecode: gen percentageexposure_nohome_lag = percentageexposure_nohome[_n-1]
by statecode: gen fullpercentageexposure_nh_lag = fullpercentageexposure_nohome[_n-1]

by statecode: gen travelspending_lag = travelspending[_n-1]

*create diffusion variables*
gen cont_birdtrail = 0 //creates a continuous dummy variable
label var cont_birdtrail "number of birding trails in state"
replace cont_birdtrail = 1 if statecode == "US-AL" & inrange(year,2000,2004)
replace cont_birdtrail = 2 if statecode == "US-AL" & inrange(year,2005,2006)
replace cont_birdtrail = 3 if statecode == "US-AL" & inrange(year,2007,2010)
replace cont_birdtrail = 4 if statecode == "US-AL" & year==2011
replace cont_birdtrail = 8 if statecode == "US-AL" & inrange(year,2012,2016)

replace cont_birdtrail = 1 if statecode == "US-AZ" & inrange(year,2006,2014)
replace cont_birdtrail = 2 if statecode == "US-AZ" & inrange(year,2015,2016)

replace cont_birdtrail = 1 if statecode == "US-CA" & inrange(year,2003,2005)
replace cont_birdtrail = 2 if statecode == "US-CA" & inrange(year,2006,2010)
replace cont_birdtrail = 3 if statecode == "US-CA" & inrange(year,2011,2016)

replace cont_birdtrail = 1 if statecode == "US-CO" & inrange(year,2000,2010)
replace cont_birdtrail = 3 if statecode == "US-CO" & inrange(year,2011,2016)

replace cont_birdtrail = 1 if statecode == "US-DE" & inrange(year,2007,2016)

replace cont_birdtrail = 1 if statecode == "US-FL" & year == 2001
replace cont_birdtrail = 2 if statecode == "US-FL" & inrange(year,2002,2003)
replace cont_birdtrail = 3 if statecode == "US-FL" & inrange(year,2004,2005)
replace cont_birdtrail = 4 if statecode == "US-FL" & inrange(year,2006,2016)

replace cont_birdtrail = 1 if statecode == "US-GA" & inrange(year,1999,2016)

replace cont_birdtrail = 4 if statecode == "US-ID" & inrange(year,2006,2016)

replace cont_birdtrail = 1 if statecode == "US-IL" & inrange(year,2005,2016)

replace cont_birdtrail = 1 if statecode == "US-LA" & inrange(year,2002,2008)
replace cont_birdtrail = 4 if statecode == "US-LA" & inrange(year,2009,2016)

replace cont_birdtrail = 1 if statecode == "US-ME" & inrange(year,2009,2016)

replace cont_birdtrail = 3 if statecode == "US-MI" & inrange(year,2013,2016)

replace cont_birdtrail = 1 if statecode == "US-MN" & inrange(year,2007,2016)

replace cont_birdtrail = 1 if statecode == "US-MS" & inrange(year,2003,2016)

replace cont_birdtrail = 1 if statecode == "US-MT" & inrange(year,2004,2005)
replace cont_birdtrail = 2 if statecode == "US-MT" & year == 2006
replace cont_birdtrail = 3 if statecode == "US-MT" & inrange(year,2007,2016)

replace cont_birdtrail = 9 if statecode == "US-NE" & inrange(year,2003,2016)

replace cont_birdtrail = 2 if statecode == "US-NJ" & inrange(year,2006,2007)
replace cont_birdtrail = 3 if statecode == "US-NJ" & inrange(year,2008,2009)
replace cont_birdtrail = 4 if statecode == "US-NJ" & inrange(year,2010,2016)

replace cont_birdtrail = 1 if statecode == "US-NC" & inrange(year,2004,2006)
replace cont_birdtrail = 2 if statecode == "US-NC" & year==2007
replace cont_birdtrail = 3 if statecode == "US-NC" & year==2008
replace cont_birdtrail = 4 if statecode == "US-NC" & inrange(year,2009,2016)

replace cont_birdtrail = 5 if statecode == "US-ND" & inrange(year,2002,2008)
replace cont_birdtrail = 6 if statecode == "US-ND" & inrange(year,2009,2016)

replace cont_birdtrail = 1 if statecode == "US-OH" & inrange(year,2012,2016)

replace cont_birdtrail = 1 if statecode == "US-OK" & inrange(year,2006,2016)

replace cont_birdtrail = 1 if statecode == "US-OR" & inrange(year,2003,2005)
replace cont_birdtrail = 3 if statecode == "US-OR" & inrange(year,2006,2008)
replace cont_birdtrail = 4 if statecode == "US-OR" & inrange(year,2009,2016)

replace cont_birdtrail = 1 if statecode == "US-PA" & inrange(year,2005,2016)

replace cont_birdtrail = 1 if statecode == "US-SD" & year == 2003
replace cont_birdtrail = 2 if statecode == "US-SD" & inrange(year,2004,2007)
replace cont_birdtrail = 3 if statecode == "US-SD" & inrange(year,2008,2016)

replace cont_birdtrail = 1 if statecode == "US-TN" & inrange(year,2010,2016)

replace cont_birdtrail = 3 if statecode == "US-TX" & inrange(year,1993,2002)
replace cont_birdtrail = 6 if statecode == "US-TX" & year==2003
replace cont_birdtrail = 8 if statecode == "US-TX" & inrange(year,2004,2010)
replace cont_birdtrail = 9 if statecode == "US-TX" & inrange(year,2011,2016)

replace cont_birdtrail = 1 if statecode == "US-UT" & inrange(year,2004,2016)

replace cont_birdtrail = 1 if statecode == "US-NH" & inrange(year,2002,2016)

replace cont_birdtrail = 1 if statecode == "US-VA" & inrange(year,2001,2002)
replace cont_birdtrail = 2 if statecode == "US-VA" & year==2003
replace cont_birdtrail = 3 if statecode == "US-VA" & inrange(year,2004,2016)

replace cont_birdtrail = 1 if statecode == "US-WA" & year==2002
replace cont_birdtrail = 2 if statecode == "US-WA" & inrange(year,2003,2004)
replace cont_birdtrail = 3 if statecode == "US-WA" & inrange(year,2005,2006)
replace cont_birdtrail = 4 if statecode == "US-WA" & inrange(year,2007,2008)
replace cont_birdtrail = 5 if statecode == "US-WA" & year==2009
replace cont_birdtrail = 6 if statecode == "US-WA" & year==2010
replace cont_birdtrail = 7 if statecode == "US-WA" & inrange(year,2011,2016)

replace cont_birdtrail = 1 if statecode == "US-WI" & year==2004
replace cont_birdtrail = 2 if statecode == "US-WI" & year==2005
replace cont_birdtrail = 3 if statecode == "US-WI" & year==2006
replace cont_birdtrail = 4 if statecode == "US-WI" & year==2007
replace cont_birdtrail = 5 if statecode == "US-WI" & inrange(year,2008,2016)

save "C:\Users\misture\OneDrive - University of Texas at Arlington\Documents\Research\As the Crow Flies\2020\Final_Data_2020.dta"   replace

clear
use "C:\Users\misture\OneDrive - University of Texas at Arlington\Documents\Research\As the Crow Flies\2020\Final_Data_2020.dta" 

//trimming missing observations
gen cont_birdtrail_trim = cont_birdtrail if N_BTbirders_nohome_lag != .

//Mechanism of diffusion
gen N_BTbirders_nohome_lag_scaled = N_BTbirders_nohome_lag/1000
label var N_BTbirders_nohome_lag_scaled "thousands of birders"

//Robustness Checks***
gen N_totalstateBTvisits_nohome_sc = N_totalstateBTvisits_nohome_lag/1000000
label var N_totalstateBTvisits_nohome_sc "millions of visits"

***Scaled versions of Primary Model/Robustness Checks***
*Xi
gen pop_log_lag	= ln(pop_lag)
label var pop_log_lag "log of population"

gen inst6013_adacope_log_lag = ln(inst6013_adacope_lag)
label var inst6013_adacope_log_lag "log of gov ideology"

gen imports_lag_scaled = imports_lag/1000000
label var imports_lag_scaled "billions of dollars"

gen spend_lag_scaled = spend_lag/1000000
label var spend_lag_scaled "billions of dollars"

gen species_lag_scaled = species_lag/1000
label var species_lag_scaled "thousands of species"

gen birddensity_lag_scaled = birddensity_lag/1000000
label var birddensity_lag_scaled "millions of birds"

*Summary Statistics*
sum cont_birdtrail
sum cont_birdtrail_trim

*Mechanism of Diffusion
sum N_BTbirders_nohome_lag
sum N_BTbirders_nohome_lag_scaled

*Robustness Checks
sum N_totalstateBTvisits_nohome_lag
sum percentexposed_nohome_lag
sum N_BTstatesvisited_nohome_lag

sum N_totalstateBTvisits_nohome_sc
sum percentexposed_nohome_lag
sum N_BTstatesvisited_nohome_lag

*Xi
sum pop_lag area
sum imports_lag spend_lag
sum citi6013_lag inst6013_adacope_lag

sum pop_log_lag inst6013_adacope_log_lag
sum imports_lag_scaled spend_lag_scaled
sum species_lag_scaled birddensity_lag_scaled


*****FINAL MODELS TO USE*****
***Descriptive statistics
sum cont_birdtrail N_BTbirders_nohome_lag_scaled inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled

***Primary Model***
xtset stcode year
xtreg cont_birdtrail N_BTbirders_nohome_lag_scaled inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year, fe
xtreg cont_birdtrail N_BTbirders_nohome_lag_scaled inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year, fe vce(robust)

*Zero-inflated Negative Binomial Model
zinb cont_birdtrail N_BTbirders_nohome_lag_scaled inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year, inflate(year) zip

***Robustness Checks***
*Zero-inflated Negative Binomial Model
zinb cont_birdtrail N_BTbirders_nohome_lag_scaled inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year i.stcode, inflate(year) zip

*alt operationalizations
xtreg cont_birdtrail N_totalstateBTvisits_nohome_sc inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year, fe
xtreg cont_birdtrail N_totalstateBTvisits_nohome_sc inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year, fe vce(robust)

xtreg cont_birdtrail percentexposed_nohome_lag inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year, fe
xtreg cont_birdtrail percentexposed_nohome_lag inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year, fe vce(robust)

xtreg cont_birdtrail N_BTstatesvisited_nohome_lag inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year, fe
xtreg cont_birdtrail N_BTstatesvisited_nohome_lag inst6013_adacope_lag pop_log_lag spend_lag_scaled imports_lag_scaled species_lag_scaled birddensity_lag_scaled year, fe vce(robust)

